Local Disk Cache in Snowflake
πΉ What is Local Disk Cache?β
When a Snowflake virtual warehouse runs a query, it needs to read data from cloud storage (Amazon S3, Azure Blob, or Google Cloud Storage). To avoid repeatedly pulling the same data from storage, Snowflake keeps a local cache of data blocks on the SSD disks of the warehouse nodes.
π This is called Local Disk Cache (sometimes also referred to as data cache).
It stores raw micro-partition data that the warehouse has recently accessed.
πΉ Why Does It Matter?β
- β Speeds up repeated queries β data is read from local SSD instead of cloud storage.
- β Reduces cloud I/O costs β fewer trips to object storage.
- β Improves concurrency β multiple queries on the same warehouse benefit from cached blocks.
- β Optimizes long-running ETL β transformations that revisit data blocks reuse cache.
β‘ But unlike Result Cache (24h) and Metadata Cache (permanent), this cache is temporary and tied to the warehouse session.
πΉ Example Scenarioβ
Table: CUSTOMER_TRANSACTIONSβ
CREATE OR REPLACE TABLE CUSTOMER_TRANSACTIONS (
ID NUMBER,
CUSTOMER_ID NUMBER,
AMOUNT NUMBER,
TRANS_DATE DATE
);
Suppose the table has 100 million rows.
Query 1 β First Runβ
SELECT SUM(AMOUNT)
FROM CUSTOMER_TRANSACTIONS
WHERE TRANS_DATE BETWEEN '2025-09-01' AND '2025-09-10';
- Warehouse reads required partitions from cloud storage.
- These data blocks are now cached locally on SSD in the warehouse.
Query 2 β Second Run (same warehouse, still active)β
SELECT AVG(AMOUNT)
FROM CUSTOMER_TRANSACTIONS
WHERE TRANS_DATE BETWEEN '2025-09-01' AND '2025-09-10';
- Snowflake detects the required blocks are already in local disk cache.
- Query reads from SSD (much faster than pulling from cloud).
- Still needs compute to aggregate, but avoids storage I/O.
πΉ When Local Disk Cache Helps Mostβ
- Repeated queries on the same warehouse.
- Workloads where queries access overlapping data ranges.
- ETL pipelines where different stages touch the same source tables.
- BI dashboards refreshed multiple times on the same warehouse.
πΉ Local Disk Cache vs Other Cachesβ
| Feature | Local Disk Cache (Warehouse SSD) | Metadata Cache (Services Layer) | Result Cache (Final Results) |
|---|---|---|---|
| Stores | Raw data blocks | Micro-partition stats (min/max, counts) | Final query output |
| Scope | Per warehouse node | Global (all sessions/users) | Global (same SQL, same role) |
| Persistence | Lost when warehouse suspends | Permanent (until data changes) | 24 hours only |
| Use Case | Speed up re-reads of same data | Partition pruning, optimize scans | Re-run identical queries instantly |
| Cost Impact | Reduces storage I/O | Reduces scanned partitions (less compute) | Skips compute entirely (0 credits) |
πΉ Limitationsβ
β Warehouse-bound β cache is not shared across different warehouses. β Lost on suspend β if a warehouse is suspended or resized, cache is cleared. β Still needs compute β unlike result cache, queries still consume credits. β Data freshness β cache may become stale if underlying table is updated (Snowflake automatically refreshes as needed).
πΉ Best Practicesβ
- β Use multi-step ETL in the same warehouse session so later steps benefit from cached blocks.
- β For BI dashboards, keep warehouses warm (not suspended) if cache benefits are critical.
- β Avoid unnecessary warehouse suspensions between frequently repeated queries.
- β Size warehouses appropriately: bigger warehouses = bigger local cache.
πΉ Visual Flow (Local Disk Cache in Action)β
Can We Control Snowflake Caches?
πΉ 1. Result Cache (24h)β
- β You cannot extend or configure its 24-hour lifespan.
- β You cannot βpre-loadβ results.
- β You can disable it per session/query:
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
or
SELECT COUNT(*)
FROM SALES_TRANSACTIONS
WHERE TRANS_DATE = '2025-09-01'
-- Disables result cache for this query
-- and forces fresh execution
;
π Use Case: Disable when you want fresh data after recent loads/updates.
πΉ 2. Metadata Cacheβ
- β You cannot disable or manually refresh this cache.
- β
Snowflake automatically manages it.
- When data changes (new micro-partitions, updates, deletes), Snowflake refreshes metadata instantly.
- As a user, you only benefit from it, no control knobs available.
πΉ 3. Local Disk Cache (Warehouse SSD)β
-
β You cannot directly enable/disable or extend it.
-
β Cache automatically warms up when a warehouse reads data.
-
β Cache is cleared when:
- Warehouse is suspended.
- Warehouse is resized.
- Data changes in storage (Snowflake invalidates stale blocks).
-
β Indirect control:
- Keep warehouse running to preserve cache.
- Use larger warehouses (more nodes β more SSD cache capacity).
- Run pipelines in the same warehouse session to reuse cached blocks.
πΉ Summary
| Cache Type | Can Enable/Disable? | Can Extend/Control? | Notes |
|---|---|---|---|
| Result Cache | β
Disable (USE_CACHED_RESULT=FALSE) | β Cannot extend beyond 24h | Good for repeated queries, 0 credits |
| Metadata Cache | β No | β No | Fully managed by Snowflake |
| Local Disk Cache | β No | β No (but indirectly via warehouse size/usage) | Lost on suspend/resize |
- β
In short:
-
Local Disk Cache = the warehouseβs short-term memory. It avoids repeatedly pulling data from cloud storage, which improves speed and reduces I/O costs. But itβs temporary, tied to the warehouse, and disappears when the warehouse suspends.
-
Snowflake fully manages caching. You can only disable result cache if you want fresh data; everything else (metadata cache & local disk cache) is automatic and outside user control.
-